USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerStatsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerStatsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStats Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE [dbo].[PlayerStatsSelectByPlayerIdLogId]

			@PlayerId varchar(30),
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio float,
		@Destructions int,
		@BuiltObjects int,
		@Dominations int,
		@Revenges int,
		@Ubers int,
		@Backstabs int,
		@Headshots int,
		@Captures int,
		@CaptureBlocks int,
		@FlagsCaptured int,
		@FlagsTouched int,
		@FlagsDefended int
		
DECLARE @SQL varchar(600)

SET @SQL = 'SELECT COUNT(AssistId) FROM Assists WHERE assister_PlayerId = ''' + @PlayerId + '''
	AND LogId in (' + @LogId + ')'

DECLARE @ResultSet TABLE (resultCount int)

--CREATE TABLE @ResultSet(resultCount int)
INSERT INTO @ResultSet EXEC(@SQL)

SELECT @Assists = resultCount FROM @ResultSet

exec PlayerNameListSelectNameById @PlayerId, @ReturnName OUTPUT


-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @PlayerId + '''
			AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet

-- Deaths
SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @PlayerId + '''
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = ''medic'')
	AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Deaths = resultCount FROM @ResultSet

-- Destruction
SET @SQL = 'SELECT COUNT(DestructionId) FROM Destructions 
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Destructions = resultCount FROM @ResultSet

-- BuiltObjects
SET @SQL = 'SELECT COUNT(EngineeringId) FROM Engineering 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @BuiltObjects = resultCount FROM @ResultSet

-- Captures
SET @SQL = 'SELECT COUNT(CaptureListId) FROM CaptureList, Captures 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND Captures.CaptureId = CaptureList.CaptureId
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Captures = resultCount FROM @ResultSet


-- CaptureBlocks

SET @SQL = 'SELECT COUNT(CaptureBlockId) FROM CaptureBlocks 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @CaptureBlocks = resultCount FROM @ResultSet

-- FlagsCaptured

SET @SQL = 'SELECT COUNT(FlagId) FROM Flag 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = ''captured'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @FlagsCaptured = resultCount FROM @ResultSet



-- FlagsTouched
SET @SQL = 'SELECT COUNT(FlagId) FROM Flag
				WHERE PlayerId = ''' + @PlayerId + '''
				AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = ''picked up'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @FlagsTouched = resultCount FROM @ResultSet

		
-- FlagsDefended
SET @SQL = 'SELECT COUNT(FlagId) FROM Flag 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = ''defended'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @FlagsDefended = resultCount FROM @ResultSet

-- Ubers

SET @SQL = 'SELECT COUNT(ChargeId) FROM MedicCharge
				WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Ubers = resultCount FROM @ResultSet

-- Dominations

SET @SQL = 'SELECT COUNT(DominationId) FROM Dominations
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Dominations = resultCount FROM @ResultSet

-- Revenges

SET @SQL = 'SELECT COUNT(RevengeId) FROM Revenges
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Revenges = resultCount FROM @ResultSet

-- Backstabs

SET @SQL = 'SELECT COUNT(KillId) FROM Kills
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = ''backstab'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Backstabs = resultCount FROM @ResultSet

-- Headshots
		
SET @SQL = 'SELECT COUNT(KillId) FROM Kills
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = ''headshot'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Headshots = resultCount FROM @ResultSet

--DROP TABLE @ResultSet


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Destructions as [Destructions], @BuiltObjects as [BuiltObjects], 
		@Dominations as [Dominations], @Revenges as [Revenges],
		@Ubers as [Ubers], @Backstabs as [Backstabs], @Headshots as [Headshots],
		@Captures as [Captures], @CaptureBlocks as [CaptureBlocks], 
		@FlagsCaptured as [FlagsCaptured], @FlagsTouched as [FlagsTouched],
		@FlagsDefended as [FlagsDefended], 
		@PlayerId as [PlayerId]



END
